/*
 * Copyright (c) Ulrich Hilger, http://dev.uhilger.de, all rights reserved.
 *
 * Published under the terms and conditions of the Apache License 2.0,
 * see http://www.apache.org/licenses/LICENSE-2.0
 */

package de.uhilger.servicehub.server;

import de.uhilger.servicehub.ServiceHub;
import de.uhilger.servicehub.model.JDBCUserStore;
import de.uhilger.servicehub.model.Role;
import de.uhilger.servicehub.model.ServletContainer;
import de.uhilger.servicehub.model.User;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;

/**
 * A user store implementation for application ServiceHub that is backed
 * by a Derby database.
 *
 * @author Ulrich Hilger, <a href="http://dev.uhilger.de" target="_blank">http://dev.uhilger.de</a>
 * @author Published under the terms and conditions of
 * the <a href="http://www.apache.org/licenses/LICENSE-2.0" target="_blank">Apache License 2.0</a>
 */
public class DerbyUserStore implements JDBCUserStore {

  /** name of derby driver to use for connections to the server */
  private static final String DERBY_DRIVER_NAME = "org.apache.derby.jdbc.ClientDriver";// "org.apache.derby.jdbc.EmbeddedDriver";
  private static final String DERBY_CONNECT_STR = "jdbc:derby://localhost:";
  private static final String DB_CREATE = ";create=true";
  private static final String SQL_CMD_SEPARATOR = ";";
  
  //private static final String USER_DB_USER = "service_hub";
  //private static final String USER_DB_PW = "storag3";
  private static final String CREATE_DB_SQL = "createDb.sql";
  private static final String CREATE_USERS_SQL = "createUsers.sql";

  private static final String CONF_DIR = "conf";

  private static final String USER_ID_COL = "USER_ID";
  private static final String ROLE_ID_COL = "ROLE_ID";

  /** name of table column having the user name */
  private static final String USER_NAME_COL = "USER_NAME";
  /** name of table column having the user password */
  private static final String USER_PASS_COL = "USER_PASS";
  /** name of table column having the role name */
  private static final String ROLE_NAME_COL = "ROLE_NAME";
  private static final String SCHEMA_NAME = "APP";
  private static final String USER_TABLE_NAME = "USERS";
  private static final String ROLE_TABLE_NAME = "ROLES";
  private static final String ROLE_MAP_TABLE_NAME = "USER_ROLES_TEXT";

  /** server base directory */
  private String base;
  /** JDBC connection to use */
  private Connection connection;
  private String userStorageName;
  private int port;
  private String user;
  private String password;

  /**
   * Create a new version of class DerbyUserStore
   *
   * @param base  server base directory. A Database of name AppServer.STORAGE_NAME
   * will be maintained in this directory. The database will be created upon
   * server start when it is not present so far.
   */
  public DerbyUserStore(String base, String userStorageName, int port) {
    super();
    this.base = base;
    this.port = port;
    this.userStorageName = userStorageName;
		try { Class.forName(AppServer.DERBY_DRIVER_NAME).newInstance(); }
		catch (Exception e) { e.printStackTrace(); }
    createDbTables();
  }

  /**
   * Get a JDBC connection to the user store
   * @return  the connection
   * @throws SQLException
   */
  private Connection getConnection() throws SQLException {
    if(connection == null || connection.isClosed() /*|| !connection.isValid(0)*/) {
      StringBuffer connect = new StringBuffer();
      connect.append(getConnectString());
      connect.append(base);
      connect.append(File.separator);
      connect.append(AppServer.STORAGE_NAME);
      connect.append(DB_CREATE);
      connection = DriverManager.getConnection(connect.toString(), user, password);
      connection.setAutoCommit(false);
    }
    return connection;
  }

  public void setDbUser(String dbUser) {
    this.user = dbUser;
  }

  /*
  private FileRef copyToTemp(Connection c) {

    FileRef tempDir = null;
    /*
    try {
      FileRef dbDir = new FileRef(base + File.separator + AppServer.STORAGE_NAME, true);
      tempDir = new FileRef(base + File.separator + "temp" + File.separator + AppServer.STORAGE_NAME, true);
      FileSystem fs = new LocalFileSystem();
      fs.copy(fs.list(dbDir), tempDir);
    } catch(Throwable t) {
      ServiceHub.logStackTrace(t, getClass());
    }
     */
    // Get today's date as a string:
    //java.text.SimpleDateFormat todaysDate =
    //new java.text.SimpleDateFormat("yyyy-MM-dd");
    //String backupdirectory = "c:/mybackups/" +
    //todaysDate.format((java.util.Calendar.getInstance()).getTime());
    /*try {
    String dest = base + File.separator + "temp/store";
    CallableStatement cs = c.prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)");
    cs.setString(1, dest);
    cs.execute();
    cs.close();
    c.commit();
    c.close();
    c = null;
    System.gc();
    FileRef dbDir = new FileRef(base + File.separator + AppServer.STORAGE_NAME, true);
    FileRef[] filesToDelete = new FileRef[1];
    filesToDelete[0] = dbDir;
    //new LocalFileSystem().delete(filesToDelete);
    new LocalFileSystem().rename(dbDir, "store_old");
    System.out.println("backed up database to "+dest);
    } catch(Throwable t) {
      ServiceHub.logStackTrace(t, getClass());
    }
    return tempDir;
  }
  */

  /*
   * INSERT INTO DEPARTMENT
   *   VALUES ('E31', 'ARCHITECTURE', '00390', 'E01', DEFAULT)
   */

  /*
  private void copyToTable(ResultSet source) {
    try {
      Connection c = getConnection();
      if(c != null && !c.isClosed()) {
        if(source.first()) {
          while(!source.isAfterLast()) {
            StringBuffer sql = new StringBuffer();
            sql.append("insert into ");
            sql.append(getSchemaName());
            sql.append(".");
            sql.append(source.getMetaData().getTableName(1));
            sql.append(" values (");
            for(int i = 0; i < source.getMetaData().getColumnCount(); i++) {
              if(i > 0) {
                sql.append(",");
              }
              sql.append("?");
            }
            sql.append(")");
            PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
            for(int i = 0; i < source.getMetaData().getColumnCount(); i++) {
              ps.setObject(i+1, source.getObject(i+1));
            }
            ps.execute();
            source.next();
          }
        }      
      }
    } catch (SQLException ex) {
      ServiceHub.logStackTrace(ex, getClass());
    }
  }
  */

  /*
  private void copyFromTable(Connection c, String tableName) {
    try {
      StringBuffer sql = new StringBuffer();
      sql.append("select * from ");
      sql.append(tableName);
      Statement s = c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      ResultSet rs = s.executeQuery(sql.toString());
      copyToTable(rs);
    } catch (SQLException ex) {
      ServiceHub.logStackTrace(ex, getClass());
    }
  }
   */

  /*
  private void buildFromTemp(String dbPass) {
    try {
      StringBuffer connect = new StringBuffer();
      connect.append(getConnectString());
      connect.append(base);
      connect.append(File.separator);
      connect.append("temp");
      connect.append(File.separator);
      connect.append(AppServer.STORAGE_NAME);
      //connect.append(DB_CREATE);
      Connection old = DriverManager.getConnection(connect.toString(), user, password);
      this.password = dbPass;    
      // create new empty database with new password
      copyFromTable(old, "app.roles");
      copyFromTable(old, "app.users");
      copyFromTable(old, "app.user_roles");
      old.close();
      old = null;
    } catch (SQLException ex) {
      ServiceHub.logStackTrace(ex, getClass());
    }
  }
   */

  public void changePassword(String dbPass) {
    // TODO: Change Password
  }

  public void setDbPassword(String dbPass) {
    this.password = dbPass;
    // TODO: change the password in the database when the server runs at the time this method is called
  }

  /**
   * Get and SQL statement stored in a package
   * @param resourceName  name of resource having SQL statement(s)
   * @return  the SQL statements from the given resource
   * @throws IOException
   */
  private String[] getSql(String resourceName) throws IOException {
    File file = new File(getBase() + File.separator + CONF_DIR + File.separator + resourceName);
    /*InputStream in = getClass().getResourceAsStream(resourceName);
    if(in == null) {
      in = ClassLoader.getSystemResourceAsStream(resourceName);
    }*/
    FileReader fileReader = new FileReader(file);
    //InputStreamReader isr = new InputStreamReader(in);
    BufferedReader br = new BufferedReader(fileReader);
    StringBuffer sql = new StringBuffer();
    String line = br.readLine();
    while (line != null) {
      sql.append(line);
      line = br.readLine();
    }
    fileReader.close();
    br.close();
    return sql.toString().split(SQL_CMD_SEPARATOR);
  }

  /**
   * Execute one or more SQL statements as a batch
   * @param c  the database connection
   * @param sqlResourceName  name of resource having SQL to execute
   * @throws SQLException
   * @throws IOException
   */
  private void executeSql(Connection c, String sqlResourceName) throws SQLException, IOException {
    Statement s = c.createStatement();
    String[] commands = getSql(sqlResourceName);
    for (int i = 0; i < commands.length; i++) {
      ServiceHub.log("executeSql adding: " + commands[i], getClass(), Level.FINEST);
      s.addBatch(commands[i]);
    }
    s.executeBatch();
    s.close();
  }

  /**
   * Create an new empty user database
   */
  public void createDbTables() {
    File file = new File(getBase() + File.separator + userStorageName);
    ServiceHub.log("createDbTables storage path: " + getBase() + File.separator + userStorageName, getClass(), Level.FINEST);
    if (!file.exists()) {
      try {
        Connection c = getConnection();
        executeSql(c, CREATE_DB_SQL);
        executeSql(c, CREATE_USERS_SQL);
        c.commit();
        c.close();
      } catch (Exception ex) {
        //ServiceHub.logStackTrace(ex, getClass());
      }
    }
  }

  /**
   * Close the data store, i.e. let the data store have the opportunity to
   * gracefully close database connecitons etc.
   * @throws SQLException
   */
  public void close() throws SQLException {
    if(connection != null && !connection.isClosed()) {
      //if(connection.isValid(0)) {
        connection.commit();
      //}
      connection.close();
    }
  }

  /**
   * Get all users in store
   * @return  a list of all users
   * @throws Exception
   */
  public List<User> getUsers() throws Exception {
    ArrayList users = null;
    Connection c = getConnection();
    StringBuffer sql = new StringBuffer();
    sql.append("select * from APP.USERS");
    PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = ps.executeQuery();
    if (rs.first()) {
      users = new ArrayList();
      while (!rs.isAfterLast()) {
        ServiceHubUser u = new ServiceHubUser();
        u.setUserId(new Integer(rs.getInt(USER_ID_COL)));
        u.setUserName(rs.getString(USER_NAME_COL));
        u.setUserPassword(rs.getString(USER_PASS_COL));
        users.add(u);
        rs.next();
      }
    }
    //c.commit();
    return users;
  }

  /**
   * Create a new user
   * @param user  the user to create
   * @return  the created user
   * @throws Exception
   */
  public Object createUser(User user) throws Exception {
    StringBuffer sql = new StringBuffer();
    sql.append("INSERT INTO APP.USERS (USER_NAME, USER_PASS) VALUES (?, ?)");
		Connection c = getConnection();
		PreparedStatement ps = c.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
    ps.setString(1, user.getUserName());
    ps.setString(2, user.getUserPassword());
    ps.executeUpdate();
    c.commit();
    ResultSet rs = ps.getGeneratedKeys();
    //if(rs.first()) {
    rs.next();
      user.setUserId(new Integer(rs.getInt(1)));
    //}
    return user;
  }

  /**
   * Store changes for an exisitng user
   * @param userId  id of user to change
   * @param user  the changed user data
   * @throws Exception
   */
  public void storeUser(Object userId, User user) throws Exception {
    Connection c = getConnection();
    StringBuffer sql = new StringBuffer();
    PreparedStatement ps;
    try {
      /*if (!userId.toString().equals(user.getUserName())) {
        sql.append("update app.user_roles");
        sql.append(" set user_name=?");
        sql.append(" where user_name=?");
        ps = c.prepareStatement(sql.toString());
        ps.setString(1, user.getUserName());
        ps.setString(2, userId.toString());
        ps.executeUpdate();
      }*/
      sql = new StringBuffer();
      sql.append("update app.users");
      sql.append(" set user_name=?");
      sql.append(", user_pass=?");
      sql.append(" where user_id=?");
      ps = c.prepareStatement(sql.toString());
      ps.setString(1, user.getUserName());
      ps.setString(2, user.getUserPassword());
      ps.setInt(3, ((Integer) userId).intValue());
        //.setString(3, userId.toString());
      ps.executeUpdate();
      c.commit();
    } catch (Exception ex) {
      c.rollback();
      throw ex;
    }
  }

  /**
   * Remove a user from the store. This will remove all role mappings
   * for this user too.
   * @param userId  id of user to remove
   * @throws Exception
   */
  public void deleteUser(Object userId) throws Exception {
    Connection c = getConnection();
    try {
      StringBuffer sql = new StringBuffer();
      sql.append("delete from app.user_roles");
      sql.append(" where user_id=?");
      PreparedStatement ps = c.prepareStatement(sql.toString());
      ps.setInt(1, ((Integer) userId).intValue());
      ps.executeUpdate();
      sql = new StringBuffer();
      sql.append("delete from app.users");
      sql.append(" where user_id=?");
      ps = c.prepareStatement(sql.toString());
      ps.setInt(1, ((Integer) userId).intValue());
      ps.executeUpdate();
      c.commit();
    } catch (Exception ex) {
      c.rollback();
      throw ex;
    }
  }

  /**
   * Get the roles of a user
   * @param userId id of user to get the roles of
   * @return  a list of all roles of the given user
   * @throws Exception
   */
  public List<Role> getUserRoles(Object userId) throws Exception {
    StringBuffer sql = new StringBuffer();
    sql.append("select app.user_roles.ROLE_ID, app.roles.ROLE_NAME from app.user_roles, app.roles");
    sql.append(" where app.user_roles.user_id=? and app.user_roles.role_id = app.roles.role_id");
		Connection c = getConnection();
		PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ps.setInt(1, ((Integer) userId).intValue());
		ArrayList roles = new ArrayList();
		ResultSet rs = ps.executeQuery();
		if (rs.first()) {
			while(!rs.isAfterLast()) {
        ServiceHubRole role = new ServiceHubRole();
        role.setRoleId(rs.getObject(ROLE_ID_COL));
        role.setRoleName(rs.getString(ROLE_NAME_COL));
				roles.add(role);
				rs.next();
			}
		}
    c.commit();
		return roles;
  }

  /**
   * Grant a role to a user
   * @param userId  id of user to grant role to
   * @param roleId  id of role to grant
   * @throws Exception
   */
  public void addUserRole(Object userId, Object roleId) throws Exception {
    StringBuffer sql = new StringBuffer();
    sql.append("INSERT INTO APP.USER_ROLES (USER_ID, ROLE_ID) VALUES (?, ?)");
		Connection c = getConnection();
		PreparedStatement ps = c.prepareStatement(sql.toString());
    ps.setInt(1, ((Integer) userId).intValue());
    ps.setInt(2, ((Integer) roleId).intValue());
    ps.executeUpdate();
    c.commit();
  }

  /**
   * Revoke a role
   * @param userId  id of user to revoke role from
   * @param roleId  id of role to revoke
   * @throws Exception
   */
  public void removeUserRole(Object userId, Object roleId) throws Exception {
    StringBuffer sql = new StringBuffer();
    sql.append("delete from app.user_roles");
    sql.append(" where user_id=? and role_id=?");
		Connection c = getConnection();
		PreparedStatement ps = c.prepareStatement(sql.toString());
    ps.setInt(1, ((Integer) userId).intValue());
    ps.setInt(2, ((Integer) roleId).intValue());
    ps.executeUpdate();
    c.commit();
  }

  /**
   * Create a role
   * @param role  the role to create
   * @return  the created role
   * @throws Exception
   */
  public Object createRole(Role role) throws Exception {
    StringBuffer sql = new StringBuffer();
    sql.append("INSERT INTO APP.ROLES (ROLE_NAME) VALUES (?)");
		Connection c = getConnection();
		PreparedStatement ps = c.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
    ps.setString(1, role.getRoleName());
    ps.executeUpdate();
    c.commit();
    ResultSet rs = ps.getGeneratedKeys();
    rs.next();
    //if(rs.first()) {
      role.setRoleId(new Integer(rs.getInt(1)));
    //}
    return role;
  }

  /**
   * Store changes to a role
   * @param roleId  id of role to change
   * @param role  the changed role to store
   * @throws Exception
   */
  public void storeRole(Object roleId, Role role) throws Exception {
    if (!roleId.toString().equals(role.getRoleName())) {
      Connection c = getConnection();
      try {
        StringBuffer sql = new StringBuffer();
        /*sql.append("update app.user_roles");
        sql.append(" set role_name=?");
        sql.append(" where role_name=?");
        PreparedStatement ps = c.prepareStatement(sql.toString());
        ps.setString(1, role.getRoleName());
        ps.setString(2, roleId.toString());
        ps.executeUpdate();
        sql = new StringBuffer();*/
        sql.append("update app.roles");
        sql.append(" set role_name=?");
        sql.append(" where role_id=?");
        PreparedStatement ps = c.prepareStatement(sql.toString());
        ps = c.prepareStatement(sql.toString());
        ps.setString(1, role.getRoleName());
        ps.setInt(2, ((Integer) roleId).intValue());
        ps.executeUpdate();
        c.commit();
      } catch (Exception ex) {
        c.rollback();
        throw ex;
      }
    }
  }

  /**
   * Remove a role from the store. This will delete all mappings
   * from all users to this role.
   * @param roleId  id of role to remove
   * @throws Exception
   */
  public void deleteRole(Object roleId) throws Exception {
    Connection c = getConnection();
    try {
      StringBuffer sql = new StringBuffer();
      sql.append("delete from app.user_roles");
      sql.append(" where role_id=?");
      PreparedStatement ps = c.prepareStatement(sql.toString());
      ps.setInt(1, ((Integer) roleId).intValue());
      ps.executeUpdate();
      sql = new StringBuffer();
      sql.append("delete from app.roles");
      sql.append(" where role_id=?");
      ps = c.prepareStatement(sql.toString());
      ps.setInt(1, ((Integer) roleId).intValue());
      ps.executeUpdate();
      c.commit();
    } catch(Exception e) {
      c.rollback();
      throw e;
    }
  }

  /**
   * Get a list of all roles in the store
   * @return  hte list of roles
   * @throws Exception
   */
  public List<Role> getRoles() throws Exception {
    StringBuffer sql = new StringBuffer();
    sql.append("select * from APP.ROLES");
		Connection c = getConnection();
		PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		ArrayList roles = new ArrayList();
		ResultSet rs = ps.executeQuery();
		if (rs.first()) {
			while(!rs.isAfterLast()) {
        ServiceHubRole role = new ServiceHubRole();
        role.setRoleId(new Integer(rs.getInt(ROLE_ID_COL)));
        role.setRoleName(rs.getString(ROLE_NAME_COL));
				roles.add(role);
				rs.next();
			}
		}
		return roles;
  }

  /**
   * Get a list of roles a user does not have
   * @param userId  id of user to get unassigned roles for
   * @return  the list of roles, the given user does not have
   * @throws Exception
   */
  public List<Role> getUnassignedRoles(Object userId) throws Exception {
    StringBuffer sql = new StringBuffer();
    sql.append("select ROLE_ID, ROLE_NAME from APP.ROLES");
    sql.append(" where ROLE_ID not in");
    sql.append(" (select ROLE_ID from APP.USER_ROLES where USER_ID=?)");
		Connection c = getConnection();
		PreparedStatement ps = c.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ps.setInt(1, ((Integer) userId).intValue());
		ArrayList roles = new ArrayList();
		ResultSet rs = ps.executeQuery();
		if (rs.first()) {
			while(!rs.isAfterLast()) {
        ServiceHubRole role = new ServiceHubRole();
        role.setRoleId(new Integer(rs.getInt(ROLE_ID_COL)));
        role.setRoleName(rs.getString(ROLE_NAME_COL));
				roles.add(role);
				rs.next();
			}
		}
		return roles;
  }

  public String getJDBCDriverClassName() {
    return DERBY_DRIVER_NAME;
  }

  public String getUserTableName() {
    return USER_TABLE_NAME;
  }

  public String getUserTableUserNameField() {
    return USER_NAME_COL;
  }

  public String getUserPasswordField() {
    return USER_PASS_COL;
  }

  public String getRoleMapTableName() {
    return ROLE_MAP_TABLE_NAME;
  }

  public String getRoleMapTableUserNameField() {
    return USER_NAME_COL;
  }

  public String getRoleMapTableRoleNameField() {
    return ROLE_NAME_COL;
  }

  public String getRoleTableName() {
    return ROLE_TABLE_NAME;
  }

  public String getRoleTableRoleNameField() {
    return ROLE_NAME_COL;
  }

  public String getSchemaName() {
    return SCHEMA_NAME;
  }

  public String getConnectString() {
    return DERBY_CONNECT_STR + port + ServletContainer.URL_SEPARATOR;
  }

  public void setBase(String base) {
    this.base = base;
  }

  public String getBase() {
    return base;
  }

  public void setUserStorageName(String name) {
    this.userStorageName = name;
  }

  public String getUSerStorageName() {
    return userStorageName;
  }

  public String getPort() {
    return Integer.toString(port);
  }

}
